package com.apes.fn.trendAnalysis;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.springframework.stereotype.Component;

/**
 * @Title: TrendAnalysisDao
 * @Description:
 * @author: ZhengZeXian
 * @create: 2021/8/17 11:03
 */
@Component
public class TrendAnalysisDao extends ApesDao {

    /**
     * 根据 type 类型转换计算周期的SQL
     * @param type DAY 天 MONTH 月
     * @return
     */
    public String getPeriodByType(String type){
        String format = type.equals("DAY") ? DateUtil.SHOW_DATE_FORMAT : DateUtil.SHOW_MONTH_FORMAT;
        String periodSQL;
        if (type.equals("DAY")){
            periodSQL = "/*周期*/" +
                    " SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:DATE_START, '" + format + "'), -12) + ROWNUM - 1, '" + format + "') CREATE_DATE\n" +
                    "   FROM DUAL\n" +
                    " CONNECT BY ROWNUM <= TO_DATE(:DATE_END, '" + format + "') - ADD_MONTHS(TO_DATE(:DATE_START, '" + format + "'), -12) + 1";
        }else {
            periodSQL = "/*周期*/" +
                    "SELECT TO_CHAR(ADD_MONTHS(ADD_MONTHS(TO_DATE(:DATE_START, '" + format + "'), ROWNUM - 1),-12), 'yyyy-MM') CREATE_DATE \n" +
                    "   FROM DUAL \n"+
                    "  CONNECT BY ROWNUM <= MONTHS_BETWEEN(TO_DATE(:DATE_END, '" + format + "'), ADD_MONTHS(TO_DATE(:DATE_START, '" + format + "'),-12)) + 1";
        }
        return periodSQL;
    }

    /**
     * 查询接车数量
     */
    public JSONArray queryPickUpCar(JSONObject param) {
        String format = param.getString("TYPE").equals("DAY") ? DateUtil.SHOW_DATE_FORMAT : DateUtil.SHOW_MONTH_FORMAT;
        getOffset(param);

        String sSQL = "SELECT D.CREATE_DATE, D.CAR_NUMBER, NVL(D.LASTYEAR, 0) LAST_CAR_NUMBER,\n" +
                "       DECODE(D.CAR_NUMBER,0,0,TO_CHAR(NVL(ROUND(DECODE(D.LASTMONTH, 0, 100, (D.CAR_NUMBER - D.LASTMONTH) / D.LASTMONTH * 100), 2), 0),\n" +
                "                'fm9999999990.00')) MONTH_RATE,\n" +
                "       DECODE(D.CAR_NUMBER,0,0,TO_CHAR(NVL(ROUND(DECODE(D.LASTYEAR, 0, 100, (D.CAR_NUMBER - D.LASTYEAR) / D.LASTYEAR * 100), 2), 0),\n" +
                "                'fm9999999990.00')) YEAR_RATE\n" +
                "  FROM (SELECT B.CREATE_DATE, NVL(C.CAR_NUMBER, 0) CAR_NUMBER,\n" +
                "                LAG(C.CAR_NUMBER, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTMONTH,\n" +
                "                LAG(C.CAR_NUMBER, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTYEAR\n" +
                "           FROM ( " + getPeriodByType(param.getString("TYPE")) + " ) B\n" +
                "           LEFT JOIN\n" +
                "             /*车流量*/\n" +
                "          (SELECT TO_CHAR(A.CREATE_DATE, '"+ format + "') CREATE_DATE, COUNT(*) CAR_NUMBER\n" +
                "            FROM FN_PICK_UP_CAR A WHERE 1=1\n" +

                /* 拼接查询条件*/
                (param.getJSONArray("DEPT_ID") != null && param.getJSONArray("DEPT_ID").size() > 0 ? (" AND A.DEPT_ID IN "+ getInParam(param.getJSONArray("DEPT_ID")) +"\n"): "") +
                (param.getJSONArray("COMPANY_ID") != null && param.getJSONArray("COMPANY_ID").size() > 0 ? (" AND A.COMPANY_ID IN "+ getInParam(param.getJSONArray("COMPANY_ID")) +"\n"): "") +
                (param.getJSONArray("AREA_ID") != null && param.getJSONArray("AREA_ID").size() > 0 ?
                        (" AND (SELECT T.DEPT_AREA_ID FROM MAS_DEPT_AREA_ITEM T WHERE T.DEPT_ID = A.DEPT_ID) " +
                                " IN "+ getInParam(param.getJSONArray("AREA_ID")) +"\n"): "") +
                (param.getJSONArray("OPERATION_ID") != null && param.getJSONArray("OPERATION_ID").size() > 0 ? (" AND (SELECT O.OPERATION FROM MAS_CORPORATE O WHERE O.PARTY_ID =(SELECT R.PARTY_ID FROM MAS_PARTY_ROLE R WHERE R.ID = A.COMPANY_ID)) IN "+ getInParam(param.getJSONArray("OPERATION_ID")) +"\n"): "") +

                "           GROUP BY TO_CHAR(A.CREATE_DATE, '" + format + "')) C\n" +
                "             ON B.CREATE_DATE = C.CREATE_DATE\n" +
                "          ORDER BY B.CREATE_DATE) D\n" +
                " WHERE D.CREATE_DATE >= :DATE_START\n" +
                "   AND D.CREATE_DATE <= :DATE_END";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询服务业绩总额
     */
    public JSONArray queryServicePerformanceMoney(JSONObject param) {
        String format = param.getString("TYPE").equals("DAY") ? DateUtil.SHOW_DATE_FORMAT : DateUtil.SHOW_MONTH_FORMAT;
        getOffset(param);

        String sSQL = "SELECT D.CREATE_DATE, D.SERVICE_PERFORMANCE_MONEY, NVL(D.LASTYEAR, 0) LAST_SERVICE_PERFORMANCE_MONEY,\n" +
                "       DECODE(D.SERVICE_PERFORMANCE_MONEY,0,0,TO_CHAR(NVL(ROUND(DECODE(D.LASTMONTH, 0, 100, (D.SERVICE_PERFORMANCE_MONEY - D.LASTMONTH) / D.LASTMONTH * 100), 2), 0),\n" +
                "                'fm9999999990.00')) MONTH_RATE,\n" +
                "       DECODE(D.SERVICE_PERFORMANCE_MONEY,0,0,TO_CHAR(NVL(ROUND(DECODE(D.LASTYEAR, 0, 100, (D.SERVICE_PERFORMANCE_MONEY - D.LASTYEAR) / D.LASTYEAR * 100), 2), 0),\n" +
                "                'fm9999999990.00')) YEAR_RATE\n" +
                "  FROM (SELECT B.CREATE_DATE, NVL(C.SERVICE_PERFORMANCE_MONEY, 0) SERVICE_PERFORMANCE_MONEY,\n" +
                "                LAG(C.SERVICE_PERFORMANCE_MONEY, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTMONTH,\n" +
                "                LAG(C.SERVICE_PERFORMANCE_MONEY, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTYEAR\n" +
                "           FROM ( " + getPeriodByType(param.getString("TYPE")) + " ) B\n" +
                "           LEFT JOIN\n" +
                "             /*服务业绩总额*/\n" +
                "          (SELECT TO_CHAR(GET_DATE(DATE_OF_STATISTICS), '" + format + "') CREATE_DATE,\n" +
                "              SUM(SALE_MONEY+INNER_LOSS_COLLECTION-FN_SETTLE_EXPENS) SERVICE_PERFORMANCE_MONEY\n" +
                "           FROM RCL_DAILYSALESREPORT A WHERE 1=1\n" +

                /* 拼接查询条件*/
                (param.getJSONArray("DEPT_ID") != null && param.getJSONArray("DEPT_ID").size() > 0 ? (" AND A.DEPT_ID IN "+ getInParam(param.getJSONArray("DEPT_ID")) +"\n"): "") +
                (param.getJSONArray("COMPANY_ID") != null && param.getJSONArray("COMPANY_ID").size() > 0 ? (" AND A.COMPANY_ID IN "+ getInParam(param.getJSONArray("COMPANY_ID")) +"\n"): "") +
                (param.getJSONArray("AREA_ID") != null && param.getJSONArray("AREA_ID").size() > 0 ?
                        (" AND (SELECT T.DEPT_AREA_ID FROM MAS_DEPT_AREA_ITEM T WHERE T.DEPT_ID = A.DEPT_ID) " +
                                " IN "+ getInParam(param.getJSONArray("AREA_ID")) +"\n"): "") +
                (param.getJSONArray("OPERATION_ID") != null && param.getJSONArray("OPERATION_ID").size() > 0 ? (" AND (SELECT O.OPERATION FROM MAS_CORPORATE O WHERE O.PARTY_ID =(SELECT R.PARTY_ID FROM MAS_PARTY_ROLE R WHERE R.ID = A.COMPANY_ID)) IN "+ getInParam(param.getJSONArray("OPERATION_ID")) +"\n"): "") +

                "           GROUP BY TO_CHAR(GET_DATE(DATE_OF_STATISTICS), '" + format + "')) C\n" +
                "             ON B.CREATE_DATE = C.CREATE_DATE\n" +
                "          ORDER BY B.CREATE_DATE) D\n" +
                " WHERE D.CREATE_DATE >= :DATE_START\n" +
                "   AND D.CREATE_DATE <= :DATE_END";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询营业额
     */
    public JSONArray querySaleMoney(JSONObject param) {
        String format = param.getString("TYPE").equals("DAY") ? DateUtil.SHOW_DATE_FORMAT : DateUtil.SHOW_MONTH_FORMAT;
        getOffset(param);

        String sSQL = "SELECT D.CREATE_DATE, D.SALE_MONEY, NVL(D.LASTYEAR, 0) LAST_SALE_MONEY,\n" +
                "       DECODE(D.SALE_MONEY,0,0,TO_CHAR(NVL(ROUND(DECODE(D.LASTMONTH, 0, 100, (D.SALE_MONEY - D.LASTMONTH) / D.LASTMONTH * 100), 2), 0),\n" +
                "                'fm9999999990.00')) MONTH_RATE,\n" +
                "       DECODE(D.SALE_MONEY,0,0,TO_CHAR(NVL(ROUND(DECODE(D.LASTYEAR, 0, 100, (D.SALE_MONEY - D.LASTYEAR) / D.LASTYEAR * 100), 2), 0),\n" +
                "                'fm9999999990.00')) YEAR_RATE\n" +
                "  FROM (SELECT B.CREATE_DATE, NVL(C.SALE_MONEY, 0) SALE_MONEY,\n" +
                "                LAG(C.SALE_MONEY, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTMONTH,\n" +
                "                LAG(C.SALE_MONEY, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTYEAR\n" +
                "           FROM ( " + getPeriodByType(param.getString("TYPE")) + " ) B\n" +
                "           LEFT JOIN\n" +
                "             /*营业额*/\n" +
                "          (SELECT TO_CHAR(GET_DATE(DATE_OF_STATISTICS), '" + format + "') CREATE_DATE,\n" +
                "              SUM(SALE_MONEY) SALE_MONEY\n" +
                "           FROM RCL_DAILYSALESREPORT A WHERE 1=1\n" +

                /* 拼接查询条件*/
                (param.getJSONArray("DEPT_ID") != null && param.getJSONArray("DEPT_ID").size() > 0 ? (" AND A.DEPT_ID IN "+ getInParam(param.getJSONArray("DEPT_ID")) +"\n"): "") +
                (param.getJSONArray("COMPANY_ID") != null && param.getJSONArray("COMPANY_ID").size() > 0 ? (" AND A.COMPANY_ID IN "+ getInParam(param.getJSONArray("COMPANY_ID")) +"\n"): "") +
                (param.getJSONArray("AREA_ID") != null && param.getJSONArray("AREA_ID").size() > 0 ?
                        (" AND (SELECT T.DEPT_AREA_ID FROM MAS_DEPT_AREA_ITEM T WHERE T.DEPT_ID = A.DEPT_ID) " +
                                " IN "+ getInParam(param.getJSONArray("AREA_ID")) +"\n"): "") +
                (param.getJSONArray("OPERATION_ID") != null && param.getJSONArray("OPERATION_ID").size() > 0 ? (" AND (SELECT O.OPERATION FROM MAS_CORPORATE O WHERE O.PARTY_ID =(SELECT R.PARTY_ID FROM MAS_PARTY_ROLE R WHERE R.ID = A.COMPANY_ID)) IN "+ getInParam(param.getJSONArray("OPERATION_ID")) +"\n"): "") +

                "           GROUP BY TO_CHAR(GET_DATE(DATE_OF_STATISTICS), '" + format + "')) C\n" +
                "             ON B.CREATE_DATE = C.CREATE_DATE\n" +
                "          ORDER BY B.CREATE_DATE) D\n" +
                " WHERE D.CREATE_DATE >= :DATE_START\n" +
                "   AND D.CREATE_DATE <= :DATE_END";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询客单价
     * (总施工产值 - 洗车产值) / (进店台数-洗车台数)
     *
     */
    public JSONArray queryGuestPrice(JSONObject param) {
        String format = param.getString("TYPE").equals("DAY") ? DateUtil.SHOW_DATE_FORMAT : DateUtil.SHOW_MONTH_FORMAT;
        getOffset(param);

        String sSQL = "SELECT E.CREATE_DATE,\n" +
                        getFormulaBySign(param.getBoolean("SIGN")) +
                "  FROM (SELECT B.CREATE_DATE, NVL(C.TOTAL_VALUE, 0) TOTAL_VALUE,NVL(C.WASH_VALUE, 0) WASH_VALUE,\n" +
                "\t                            NVL(D.STORE_NUMBER, 0) STORE_NUMBER,NVL(D.WASH_NUMBER, 0) WASH_NUMBER,\n" +
                "\t             /* 总产值 - 洗车产值 当前 上月 上年*/                    \n" +
                "               NVL((C.TOTAL_VALUE - C.WASH_VALUE), 0) DIFF_VALUE,\n" +
                "               NVL(LAG(C.TOTAL_VALUE, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) - LAG(C.WASH_VALUE, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE),0) LASTMONTH_DIFF_VALUE,\n" +
                "               NVL(LAG(C.TOTAL_VALUE, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) - LAG(C.WASH_VALUE, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE),0) LASTYEAR_DIFF_VALUE,\n" +
                "               /* 总台数 - 洗车台数 当前 上月 上年*/ \n" +
                "               NVL((D.STORE_NUMBER - D.WASH_NUMBER), 0) DIFF_NUMBER,\n" +
                "               NVL(LAG(D.STORE_NUMBER, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) - LAG(D.WASH_NUMBER, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE),0) LASTMONTH_DIFF_NUMBER,\n" +
                "               NVL(LAG(D.STORE_NUMBER, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) - LAG(D.WASH_NUMBER, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE),0) LASTYEAR_DIFF_NUMBER,\n" +
                "               /*往期数据*/   " +
                "                LAG(C.TOTAL_VALUE, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTMONTH_TOTAL_VALUE,\n" +
                "                LAG(C.TOTAL_VALUE, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTYEAR_TOTAL_VALUE,\n" +
                "                LAG(C.WASH_VALUE, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTMONTH_WASH_VALUE,\n" +
                "                LAG(C.WASH_VALUE, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTYEAR_WASH_VALUE,\n" +
                "                LAG(D.STORE_NUMBER, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTMONTH_STORE_NUMBER,\n" +
                "                LAG(D.STORE_NUMBER, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTYEAR_STORE_NUMBER,\n" +
                "                LAG(D.WASH_NUMBER, " + param.getInteger("monthOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTMONTH_WASH_NUMBER,\n" +
                "                LAG(D.WASH_NUMBER, " + param.getInteger("yearOffset") + ", 0) OVER(ORDER BY B.CREATE_DATE) LASTYEAR_WASH_NUMBER\n" +
                "           FROM ( " + getPeriodByType(param.getString("TYPE")) + " ) B\n" +
                "           LEFT JOIN\n" +
                "             /*总施工产值 洗车产值*/\n" +
                "          (SELECT TO_CHAR(A.TJRQ, '" + format + "') CREATE_DATE,\n" +
                "              SUM(A.WORK_OUTPUTVALUE_ALL) TOTAL_VALUE,SUM(A.WASH_OUTPUTVALUE) WASH_VALUE \n" +
                "           FROM RPT_WORK_OUTPUT_VALUE A WHERE 1=1 \n" +

                /* 拼接查询条件*/
                (param.getJSONArray("DEPT_ID") != null && param.getJSONArray("DEPT_ID").size() > 0 ? (" AND A.DEPT_ID IN "+ getInParam(param.getJSONArray("DEPT_ID")) +"\n"): "") +
                (param.getJSONArray("COMPANY_ID") != null && param.getJSONArray("COMPANY_ID").size() > 0 ? (" AND A.COMPANY_ID IN "+ getInParam(param.getJSONArray("COMPANY_ID")) +"\n"): "") +
                (param.getJSONArray("AREA_ID") != null && param.getJSONArray("AREA_ID").size() > 0 ?
                        (" AND (SELECT T.DEPT_AREA_ID FROM MAS_DEPT_AREA_ITEM T WHERE T.DEPT_ID = A.DEPT_ID) " +
                                " IN "+ getInParam(param.getJSONArray("AREA_ID")) +"\n"): "") +
                (param.getJSONArray("OPERATION_ID") != null && param.getJSONArray("OPERATION_ID").size() > 0 ? (" AND (SELECT O.OPERATION FROM MAS_CORPORATE O WHERE O.PARTY_ID =(SELECT R.PARTY_ID FROM MAS_PARTY_ROLE R WHERE R.ID = A.COMPANY_ID)) IN "+ getInParam(param.getJSONArray("OPERATION_ID")) +"\n"): "") +

                "           GROUP BY TO_CHAR(A.TJRQ, '" + format + "')) C\n" +
                "                 ON B.CREATE_DATE = C.CREATE_DATE\n" +
                "           LEFT JOIN\n" +
                "             /*进店台数 洗车台数*/\n" +
                "           (SELECT TO_CHAR(GET_DATE(DATE_OF_STATISTICS), '" + format + "') CREATE_DATE,\n" +
                "              SUM(A.TIMES_OF_ENTERING_THE_STORE) STORE_NUMBER,SUM(A.CAR_WASH_TIMES) WASH_NUMBER\n" +
                "           FROM RCL_DAILYSALESREPORT A WHERE 1 = 1\n" +

                /* 拼接查询条件*/
                (param.getJSONArray("DEPT_ID") != null && param.getJSONArray("DEPT_ID").size() > 0 ? (" AND A.DEPT_ID IN "+ getInParam(param.getJSONArray("DEPT_ID")) +"\n"): "") +
                (param.getJSONArray("COMPANY_ID") != null && param.getJSONArray("COMPANY_ID").size() > 0 ? (" AND A.COMPANY_ID IN "+ getInParam(param.getJSONArray("COMPANY_ID")) +"\n"): "") +
                (param.getJSONArray("AREA_ID") != null && param.getJSONArray("AREA_ID").size() > 0 ?
                        (" AND (SELECT T.DEPT_AREA_ID FROM MAS_DEPT_AREA_ITEM T WHERE T.DEPT_ID = A.DEPT_ID) " +
                                " IN "+ getInParam(param.getJSONArray("AREA_ID")) +"\n"): "") +
                (param.getJSONArray("OPERATION_ID") != null && param.getJSONArray("OPERATION_ID").size() > 0 ? (" AND (SELECT O.OPERATION FROM MAS_CORPORATE O WHERE O.PARTY_ID =(SELECT R.PARTY_ID FROM MAS_PARTY_ROLE R WHERE R.ID = A.COMPANY_ID)) IN "+ getInParam(param.getJSONArray("OPERATION_ID")) +"\n"): "") +

                "           GROUP BY TO_CHAR(GET_DATE(DATE_OF_STATISTICS), '" + format + "')) D\n" +
                "             ON B.CREATE_DATE = D.CREATE_DATE\n" +
                "          ORDER BY B.CREATE_DATE) E\n" +
                " WHERE E.CREATE_DATE >= :DATE_START\n" +
                "   AND E.CREATE_DATE <= :DATE_END";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 根据标记 获取 计算公式 客单价
     * @return
     */
    public String getFormulaBySign(Boolean sign){
        String formula;
        //勾选标记 总产值 / 总台数
        if (sign){
            formula = "NVL(ROUND(DECODE(E.STORE_NUMBER, 0, 0, E.TOTAL_VALUE / E.STORE_NUMBER), 2), 0) PRICE,\n" +
                    "       NVL(ROUND(DECODE(E.LASTYEAR_STORE_NUMBER, 0, 0, E.LASTYEAR_TOTAL_VALUE / E.LASTYEAR_STORE_NUMBER), 2), 0) LAST_PRICE,\n" +
                    "       ROUND(DECODE(E.LASTMONTH_STORE_NUMBER, 0, 0, E.LASTMONTH_TOTAL_VALUE / E.LASTMONTH_STORE_NUMBER), 2),\n" +
                    "       DECODE(ROUND(DECODE(E.STORE_NUMBER, 0, 0, E.TOTAL_VALUE / E.STORE_NUMBER), 2), 0, 0,\n" +
                    "               TO_CHAR(NVL(ROUND\n" +
                    "                   (DECODE(ROUND(DECODE(E.LASTMONTH_STORE_NUMBER, 0, 0, E.LASTMONTH_TOTAL_VALUE / E.LASTMONTH_STORE_NUMBER), 2), 0, 100,\n" +
                    "                       DECODE(ROUND(DECODE(E.LASTMONTH_STORE_NUMBER, 0, 0, E.LASTMONTH_TOTAL_VALUE / E.LASTMONTH_STORE_NUMBER), 2),0,0,\n" +
                    "                           (ROUND(DECODE(E.STORE_NUMBER, 0, 0, E.TOTAL_VALUE / E.STORE_NUMBER), 2) -\n" +
                    "                                          ROUND(DECODE(E.LASTMONTH_STORE_NUMBER, 0, 0, E.LASTMONTH_TOTAL_VALUE / E.LASTMONTH_STORE_NUMBER), 2)) /\n" +
                    "                                          ROUND(DECODE(E.LASTMONTH_STORE_NUMBER, 0, 0, E.LASTMONTH_TOTAL_VALUE / E.LASTMONTH_STORE_NUMBER), 2) * 100)), 2), 0),\n" +
                    "                        'fm9999999990.00')) MONTH_RATE,\n" +
                    "       DECODE(ROUND(DECODE(E.STORE_NUMBER, 0, 0, E.TOTAL_VALUE / E.STORE_NUMBER), 2), 0, 0,\n" +
                    "               TO_CHAR(NVL(ROUND\n" +
                    "                   (DECODE(ROUND(DECODE(E.LASTYEAR_STORE_NUMBER, 0, 0, E.LASTYEAR_TOTAL_VALUE / E.LASTYEAR_STORE_NUMBER), 2), 0, 100,\n" +
                    "                       DECODE(ROUND(DECODE(E.LASTYEAR_STORE_NUMBER, 0, 0, E.LASTYEAR_TOTAL_VALUE / E.LASTYEAR_STORE_NUMBER), 2),0,0,\n" +
                    "                           (ROUND(DECODE(E.STORE_NUMBER, 0, 0, E.TOTAL_VALUE / E.STORE_NUMBER), 2) -\n" +
                    "                                          ROUND(DECODE(E.LASTYEAR_STORE_NUMBER, 0, 0, E.LASTYEAR_TOTAL_VALUE / E.LASTYEAR_STORE_NUMBER), 2)) /\n" +
                    "                                          ROUND(DECODE(E.LASTYEAR_STORE_NUMBER, 0, 0, E.LASTYEAR_TOTAL_VALUE / E.LASTYEAR_STORE_NUMBER), 2) * 100)), 2), 0),\n" +
                    "                        'fm9999999990.00')) YEAR_RATE";
        }else {
            formula = " NVL(ROUND(DECODE(E.DIFF_NUMBER, 0, 0, E.DIFF_VALUE / E.DIFF_NUMBER), 2), 0) PRICE,\n" +
                    "       NVL(ROUND(DECODE(E.LASTYEAR_DIFF_NUMBER, 0, 0, E.LASTYEAR_DIFF_VALUE / E.LASTYEAR_DIFF_NUMBER), 2), 0) LAST_PRICE,\n" +
                    "       DECODE(ROUND(DECODE(E.DIFF_NUMBER, 0, 0, E.DIFF_VALUE / E.DIFF_NUMBER), 2), 0, 0,\n" +
                    "               TO_CHAR(NVL(ROUND(DECODE(ROUND(DECODE(E.LASTMONTH_DIFF_NUMBER, 0, 0,\n" +
                    "                                                      E.LASTMONTH_DIFF_VALUE / E.LASTMONTH_DIFF_NUMBER), 2), 0, 100,\n" +
                    "                                         DECODE(ROUND(DECODE(E.LASTMONTH_DIFF_NUMBER, 0, 0,\n" +
                    "                                                              E.LASTMONTH_DIFF_VALUE / E.LASTMONTH_DIFF_NUMBER), 2), 0, 0,\n" +
                    "                                                 (ROUND(DECODE(E.DIFF_NUMBER, 0, 0, E.DIFF_VALUE / E.DIFF_NUMBER), 2) -\n" +
                    "                                                  ROUND(DECODE(E.LASTMONTH_DIFF_NUMBER, 0, 0,\n" +
                    "                                                                E.LASTMONTH_DIFF_VALUE / E.LASTMONTH_DIFF_NUMBER), 2)) /\n" +
                    "                                                  ROUND(DECODE(E.LASTMONTH_DIFF_NUMBER, 0, 0,\n" +
                    "                                                               E.LASTMONTH_DIFF_VALUE / E.LASTMONTH_DIFF_NUMBER), 2) * 100)),\n" +
                    "                                  2), 0), 'fm9999999990.00')) MONTH_RATE,\n" +
                    "       DECODE(ROUND(DECODE(E.DIFF_NUMBER, 0, 0, E.DIFF_VALUE / E.DIFF_NUMBER), 2), 0, 0,\n" +
                    "               TO_CHAR(NVL(ROUND(DECODE(ROUND(DECODE(E.LASTYEAR_DIFF_NUMBER, 0, 0,\n" +
                    "                                                      E.LASTYEAR_DIFF_VALUE / E.LASTYEAR_DIFF_NUMBER), 2), 0, 100,\n" +
                    "                                         DECODE(ROUND(DECODE(E.LASTMONTH_DIFF_NUMBER, 0, 0,\n" +
                    "                                                              E.LASTMONTH_DIFF_VALUE / E.LASTMONTH_DIFF_NUMBER), 2), 0, 0,\n" +
                    "                                                 (ROUND(DECODE(E.DIFF_NUMBER, 0, 0, E.DIFF_VALUE / E.DIFF_NUMBER), 2) -\n" +
                    "                                                  ROUND(DECODE(E.LASTYEAR_DIFF_NUMBER, 0, 0,\n" +
                    "                                                                E.LASTYEAR_DIFF_VALUE / E.LASTYEAR_DIFF_NUMBER), 2)) /\n" +
                    "                                                  ROUND(DECODE(E.LASTMONTH_DIFF_NUMBER, 0, 0,\n" +
                    "                                                               E.LASTMONTH_DIFF_VALUE / E.LASTMONTH_DIFF_NUMBER), 2)) * 100),\n" +
                    "                                  2), 0), 'fm9999999990.00')) YEAR_RATE";
        }
        return formula;
    }

    /***
     * String数组转 ('1', '2', '3')
     */
    private String getInParam(JSONArray param){
        String str = param.stream().reduce("",(a, b) -> {
            if (a.equals("")) return "'" + b + "'";
            return a + ", '" + b + "'";
        },String::concat);

        return "(" + str + ")";
    }

    /**
     * 计算偏移量
     */
    private void getOffset(JSONObject param){
        param.put("monthOffset" , param.getString("TYPE").equals("DAY") ? DateUtil.dateCompare(param.getDate("DATE_END"),param.getDate("DATE_START")) == 0 ? 1 : (param.getDate("DATE_END").getTime() - param.getDate("DATE_START").getTime()) / (1000L*3600L*24L) + 1 : 1);
        param.put("yearOffset" , param.getString("TYPE").equals("DAY") ? 365 : 12);

    }
}
